use test;
show TABLES ;
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
--  Table structure for `websites`
-- ----------------------------
DROP TABLE IF EXISTS `websites`;
CREATE TABLE `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `websites`
-- ----------------------------
BEGIN;
INSERT INTO `websites` VALUES ('1', 'Google', 'https://www.google.cm/', '1', 'USA'), ('2', '淘宝', 'https://www.taobao.com/', '13', 'CN'), ('3', '菜鸟教程', 'http://www.runoob.com/', '4689', 'CN'), ('4', '微博', 'http://weibo.com/', '20', 'CN'), ('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;


SHOW tables;
SHOW CREATE TABLE websites;
SELECT * FROM test.websites;
SELECT id,name FROM test.websites;

SELECT DISTINCT country FROM test.websites;

# %	替代 0 个或多个字符
# _	替代一个字符
# [charlist]	字符列中的任何单一字符
# [^charlist]
# [!charlist]	不在字符列中的任何单一字符
SELECT * FROM test.websites WHERE websites.alexa<5;
SELECT * FROM test.websites WHERE websites.country='CN';

SELECT * FROM test.websites WHERE websites.name REGEXP '^[GFs百]';
SELECT * FROM test.websites WHERE websites.name REGEXP  '^[^A-H]';;
SELECT * FROM test.websites WHERE websites.name REGEXP  '^G';;
SELECT * FROM test.websites WHERE websites.name REGEXP  'G';;
SELECT * FROM test.websites WHERE websites.name REGEXP  '百度';;

SELECT * FROM test.websites WHERE websites.country LIKE '%N';
SELECT * FROM test.websites WHERE websites.country LIKE '__';
SELECT * FROM test.websites WHERE websites.country LIKE '%';
SELECT * FROM test.websites WHERE websites.country LIKE '_N';
SELECT * FROM test.websites WHERE websites.country LIKE '_S_';
SELECT * FROM test.websites WHERE websites.country LIKE '_%_';
SELECT * FROM test.websites WHERE websites.url LIKE '%https%';

SELECT * FROM test.websites WHERE websites.country='USA' AND websites.name LIKE 'G%';


# ORDER BY
SELECT * FROM websites ORDER BY alexa; #升序
SELECT * FROM websites ORDER BY alexa DESC; #降序
SELECT * FROM websites ORDER BY id ASC ;
SELECT * FROM websites ORDER BY alexa DESC ;
SELECT * FROM websites ORDER BY alexa,id;
SELECT * FROM websites ORDER BY alexa DESC ,id DESC ;

INSERT INTO websites(name,url,alexa,country) VALUES ('百度','https://www.baidu.com/','4','CN');
INSERT INTO websites(name,url,alexa,country) VALUES ('PHP','http://php.net','40','USA');
INSERT INTO websites (name, url, country) VALUES ('stackoverflow', 'http://stackoverflow.com/', 'IND');


UPDATE websites SET alexa = 30 WHERE country LIKE 'I__';


DELETE FROM websites WHERE name='baidu';

SELECT * FROM websites LIMIT 3;
SELECT * FROM websites LIMIT 0,2;
SELECT * FROM websites LIMIT 1,2;


SELECT name as 名字,url 地址 FROM websites as w WHERE w.alexa < 100;

-- ----------------------------
--  Table structure for `access_log`
-- ----------------------------
DROP TABLE IF EXISTS `access_log`;
CREATE TABLE `access_log` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL DEFAULT '0' COMMENT '网站id',
  `count` int(11) NOT NULL DEFAULT '0' COMMENT '访问次数',
  `date` date NOT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `access_log`
-- ----------------------------
BEGIN;
INSERT INTO `access_log` VALUES ('1', '1', '45', '2016-05-10'), ('2', '3', '100', '2016-05-13'), ('3', '1', '230', '2016-05-14'), ('4', '2', '10', '2016-05-14'), ('5', '5', '205', '2016-05-14'), ('6', '4', '13', '2016-05-15'), ('7', '3', '220', '2016-05-15'), ('8', '5', '545', '2016-05-16'), ('9', '3', '201', '2016-05-17');
COMMIT;

FLUSH TABLE websites;
FLUSH TABLE access_log;
SELECT * FROM access_log;
SELECT * FROM access_log,websites;
SELECT websites.name, access_log.count, access_log.date FROM access_log,websites WHERE access_log.site_id = websites.id ORDER BY access_log.count;

ANALYZE TABLE access_log;

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
  INNER JOIN access_log
    ON Websites.id=access_log.site_id
ORDER BY access_log.count;

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
  LEFT JOIN access_log
    ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

SELECT Websites.name, access_log.count, access_log.date
FROM access_log
  RIGHT JOIN Websites
    ON access_log.site_id=Websites.id
ORDER BY access_log.count DESC;

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
      JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

DROP TABLE IF EXISTS `apps`;
CREATE TABLE `apps` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `app_name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
  `url` varchar(255) NOT NULL DEFAULT '',
  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

BEGIN;
INSERT INTO `apps` VALUES ('1', 'QQ APP', 'http://im.qq.com/', 'CN'), ('2', '微博 APP', 'http://weibo.com/', 'CN'), ('3', '淘宝 APP', 'https://www.taobao.com/', 'CN');
COMMIT;
INSERT INTO `apps` (app_name, url, country) VALUES
  ('java', 'http://www.java.com/', 'USA'),
  ('scala', 'http://scala.com/', 'WX'),
  ('groovy', 'https://www.groovy.com/', 'WX');


SELECT * FROM apps;

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

CREATE TABLE new_apps SELECT * FROM apps;
SELECT * FROM new_apps;
TRUNCATE TABLE new_apps;
INSERT INTO new_apps SELECT * FROM apps;
CREATE TABLE new_apps_bak SELECT id,app_name,url FROM apps;
SELECT * FROM new_apps_bak;

CREATE DATABASE mytest;
USE mytest;
CREATE TABLE student(
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(10) DEFAULT 'zrx' ,
  sex VARCHAR(4) DEFAULT 'boy',
  age INT DEFAULT 20 NOT NULL
);
BEGIN;
INSERT INTO student (name,sex,age) VALUE('zrx','boy',22);
COMMIT ;
ROLLBACK;
SELECT * FROM student;

# NOT NULL - 指示某列不能存储 NULL 值。
# UNIQUE - 保证某列的每行必须有唯一的值。
# PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
# FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
# CHECK - 保证列中的值符合指定的条件。
# DEFAULT - 规定没有给列赋值时的默认值。

DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test(
  id INT,
  unique1 VARCHAR(10),
  unique2 TEXT,
  unique3 LONG,
  unique4 DATE,
#   CONSTRAINT unique1_id UNIQUE (id,unique1),
  PRIMARY KEY (id),
  CHECK (unique2)
)CHARACTER SET utf8 ;
FLUSH TABLE t_test;
# ALTER TABLE t_test DROP INDEX unique1_id
DROP TABLE IF EXISTS Persons;
CREATE TABLE persons(
  id INT,
  name VARCHAR(20)
);
DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders
(
  id int NOT NULL,
  orderno int NOT NULL,
  p_id int,
  PRIMARY KEY (id),
  FOREIGN KEY (p_id) REFERENCES Persons(id)
);


SELECT NOW();
SELECT UCASE('wocao');
SELECT LCASE('WOCAO');
# MID() - 从某个文本字段提取字符
# LEN() - 返回某个文本字段的长度
SELECT LENGTH('haha');
# ROUND() - 对某个数值字段进行指定小数位数的四舍五入
SELECT ROUND(3.4);
SELECT ROUND(3.5);
# NOW() - 返回当前的系统日期和时间
# FORMAT() - 格式化某个字段的显示方式

SELECT * FROM student WHERE id IS NULL ;
SELECT * FROM student WHERE id IS NOT NULL ;

SELECT * FROM information_schema.plugins;
SELECT @@innodb_version;

# This is a pseudo storage engine to represent the binlog in a transaction
SELECT PLUGIN_DESCRIPTION FROM information_schema.PLUGINS WHERE PLUGIN_NAME ='binlog';

SHOW COLUMNS FROM student;
SHOW STATUS;
SHOW GRANTS ;
SHOW ERRORS ;
SHOW WARNINGS ;

SELECT time(now()) ;
SELECT year(now()) ;

SELECT curdate();
SELECT curtime();
SELECT curdate(),curtime();

CREATE VIEW view_student AS SELECT * FROM student;
SELECT * FROM view_student;
DROP VIEW view_student;


CREATE TEMPORARY TABLE temp_student AS SELECT * FROM student;
SELECT * FROM temp_student;
DROP TEMPORARY TABLE  IF EXISTS temp_student;

CREATE PROCEDURE pro_student(IN pro_name INT)
  BEGIN
    SELECT * FROM student;
  END;

CALL pro_student();
DROP PROCEDURE IF EXISTS pro_student;